/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package DAO;
import DTO.*;
import UTIL.SqlDataAccessHelper;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Time;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
/**
 *
 * @author ldtung_it
 */
public class TasksDAO {
    /**
     * add a task
     * @param task
     * @return
     * @throws java.sql.SQLException
     * @throws java.sql.SQLException
     */
    public static int add(TasksDTO task) throws SQLException, SQLException {
        Connection connect = null;
        try {
            connect = SqlDataAccessHelper.getConnection();
            Statement stmt = (Statement) connect.createStatement();
            Date date = task.getDateTask();
            SimpleDateFormat dateFormatter = new SimpleDateFormat("MM/dd/yyyy hh:mm:ss aa");

            String sqlQuery = "insert into Tasks(IDContacts,DateTask,Events) values(" + task.getIDContact() + ",'" + dateFormatter.format(date) + "','" + task.getEvent()+  "')";
            stmt.executeUpdate(sqlQuery);


            stmt.close();
            connect.close();
            return MailDAO.getIDMail();
        } catch (SQLException ex) {
            //fail
            connect.close();
        }
        
        return 0;
    }
    /**
     * delete a task
     * @param idTask
     * @return
     * @throws java.sql.SQLException
     */
    public static int delete(Calendar date, int idContact) throws SQLException {
        Connection connect = null;
        try {
            connect = SqlDataAccessHelper.getConnection();
            Statement stmt = (Statement) connect.createStatement();
            String sqlQuery = "";
            int month = date.get(Calendar.MONTH)+ 1;
            sqlQuery = "delete from Tasks t where t.IDContacts = " + idContact + " and day(t.DateTask) = "+ date.get(Calendar.DAY_OF_MONTH)  +" and month(t.DateTask) = " + month  + " and year(t.DateTask)  = " +  date.get(Calendar.YEAR) + " and hour(t.DateTask)  = " +  date.get(Calendar.HOUR_OF_DAY) + " and minute(t.DateTask)  = " +  date.get(Calendar.MINUTE);
            stmt.executeUpdate(sqlQuery);
            stmt.close();
            connect.close();
            return 1;
        } catch (SQLException ex) {
            //fail
            connect.close();
        }
        return 0;
    }
    public static int update(Calendar date, int idContact,String newEvent) throws SQLException {
        Connection connect = null;
        try {
            connect = SqlDataAccessHelper.getConnection();
            Statement stmt = (Statement) connect.createStatement();
            String sqlQuery = "";
            int month = date.get(Calendar.MONTH)+ 1;
            //sqlQuery = "delete from Tasks t where t.IDContacts = " + idContact + " and day(t.DateTask) = "+ date.get(Calendar.DAY_OF_MONTH)  +" and month(t.DateTask) = " + month  + " and year(t.DateTask)  = " +  date.get(Calendar.YEAR) + " and hour(t.DateTask)  = " +  date.get(Calendar.HOUR_OF_DAY) + " and minute(t.DateTask)  = " +  date.get(Calendar.MINUTE);
            sqlQuery = "update Tasks t set t.Events = '" + newEvent + "' where t.IDContacts = " + idContact + " and day(t.DateTask) = "+ date.get(Calendar.DAY_OF_MONTH)  +" and month(t.DateTask) = " + month  + " and year(t.DateTask)  = " +  date.get(Calendar.YEAR) + " and hour(t.DateTask)  = " +  date.get(Calendar.HOUR_OF_DAY) + " and minute(t.DateTask)  = " +  date.get(Calendar.MINUTE);
            stmt.executeUpdate(sqlQuery);
            stmt.close();
            connect.close();
            return 1;
        } catch (SQLException ex) {
            //fail
            connect.close();
        }
        return 0;
    }
    public static ArrayList<TasksDTO> getTask(Date date,int idContact) throws SQLException{
        Connection connect = null;
        ArrayList<TasksDTO> alTask = new ArrayList<TasksDTO>();
        try {
            connect = SqlDataAccessHelper.getConnection();
            Statement stmt = (Statement) connect.createStatement();
            Calendar cal = Calendar.getInstance();
            cal.setTime(date);
            SimpleDateFormat dateFormatter = new SimpleDateFormat("MM/dd/yyyy hh:mm:ss aa");
            int month = cal.get(Calendar.MONTH) + 1;
            String sqlQuery = "select * from Tasks t where t.IDContacts = "+ idContact +" and day(t.DateTask) = "+ cal.get(Calendar.DAY_OF_MONTH)  +" and month(t.DateTask) = " + month  + " and year(t.DateTask)  = " +  cal.get(Calendar.YEAR);
            //String sqlQuery = "select * from Tasks t where day(t.DateTask) = 30";
            ResultSet rs = stmt.executeQuery(sqlQuery);
            
            while(rs.next()){
                TasksDTO task = new TasksDTO();
                task.setIDCount(rs.getInt(1));
                task.setIDContact(rs.getInt(2));
                task.setDateTask(rs.getTimestamp(3));
                
                task.setEvent(rs.getString(4));
                alTask.add(task);
            }

            stmt.close();
            connect.close();
            return alTask;
        } catch (SQLException ex) {
            //fail
        }
        connect.close();
        return alTask;
    }
    public static ArrayList<TasksDTO> getTaskMonth(int month,int year,int idContact) throws SQLException{
        Connection connect = null;
        ArrayList<TasksDTO> alTask = new ArrayList<TasksDTO>();
        try {
            connect = SqlDataAccessHelper.getConnection();
            Statement stmt = (Statement) connect.createStatement();
            
           
            
            String sqlQuery = "select * from Tasks t where t.IDContacts = "+ idContact + " and month(t.DateTask) = " + month  + " and year(t.DateTask)  = " +  year;
            //String sqlQuery = "select * from Tasks t where day(t.DateTask) = 30";
            ResultSet rs = stmt.executeQuery(sqlQuery);

            while(rs.next()){
                TasksDTO task = new TasksDTO();
                task.setIDCount(rs.getInt(1));
                task.setIDContact(rs.getInt(2));
                task.setDateTask(rs.getTimestamp(3));

                task.setEvent(rs.getString(4));
                alTask.add(task);
            }

            stmt.close();
            connect.close();
            return alTask;
        } catch (SQLException ex) {
            //fail
        }
        connect.close();
        return alTask;
    }
}
